<?php

/**
 * Description of SupportController
 *
 * @author Francis Adu-Gyamfi <francis.adu-gyamfi@matrixdesigns.org>
 */
class SupportController extends AppController {
    //put your code here
    
    public $components = array('Api');
    
    public function beforeFilter() {
        parent::beforeFilter();
        
        // actions that don't need authentication
        $actions = array('login', 'logout', 'unlock');

        // check if user is not authenticated and request login
        if ( !$this->Session->check('support_account') && !in_array($this->action, $actions)) {
            $this->redirect(array('action' => 'login'));
        }
        
        $this->layout = 'support';
    }
    
    /**
     * Home page
     */
    public function index() {
        $response = $this->Api->post('/support/organisations', array());
        
        $this->set('organisations', $response['data']);
        $this->set('title_for_layout', 'Dashboard');
    }
    
    public function login() {
        $this->layout = "login";
        
        if( isset($_POST['username']) ) {
            $response = $this->Api->post('/support/login', array(
                'username' => $_POST['username'],
                'password' => $_POST['password']
            ));

            if( $response['status'] == 'success' ) {
                $this->Session->write('support_account', $response['account']);
                $this->redirect(array('action' => 'index'));
            }
            
            $this->Session->setFlash($response['message']);
            
            return;
        }
    }
    
    public function logout() {
        
        $this->Session->delete('support_account');
        
        $this->redirect(array('controller' => 'support', 'action' => 'login'));
    }
    
    public function data() {
        return $this->data_to_file();
        
        if( isset($_POST['organisation_id']) ) {
            $this->autoRender = false;
            
            $model = ClassRegistry::init('Organisation');
            $organisation_id = $_POST['organisation_id'];
            
            $filename = "export_organisation_id_$organisation_id.sql";
            header("Content-Disposition: attachment; filename=\"$filename\"");
            header("Content-Type: text/sql");
            
            echo "SET FOREIGN_KEY_CHECKS = 0; \n\n";

            // backup organisation specific data
            $result = $model->query("SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE (COLUMN_NAME IN ('organisation_id') AND TABLE_SCHEMA='organisations') OR (TABLE_NAME = 'organisations');");
            
            foreach($result as $row) {
                $table_name = $row['COLUMNS']['TABLE_NAME'];
                
                $tbl_result = null;
                
                if( $table_name != 'organisations' ) {
                    $tbl_result = $model->query("SELECT * FROM $table_name WHERE organisation_id = $organisation_id");
                }
                
                else {
                    $tbl_result = $model->query("SELECT * FROM $table_name WHERE id = $organisation_id");
                }
                
                foreach($tbl_result as $tbl_row) {
                    $tbl_row_data = $tbl_row[$table_name];
                    $columns = array_keys($tbl_row_data);
                    $values = array_values($tbl_row_data);
                    array_walk($values, function(&$value) {
                        $value = mysql_escape_string($value);
                    });
                    
                    $col_string = "`" . implode("`,`", $columns) . "`";
                    $val_string = "'" . implode("','", $values) . "'";
                    
                    echo "REPLACE INTO `organisations`.`$table_name` ($col_string) VALUES ($val_string);\n";
                }
                
                echo "\n";
            }
            
            // backup related member records
            $result2 = $model->query("SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE (COLUMN_NAME IN ('member_id') AND TABLE_SCHEMA='memberz') OR TABLE_NAME = 'members';");
            
            foreach($result2 as $row) {
                $table_name = $row['COLUMNS']['TABLE_NAME'];
                
                $tbl_result = null;
                
                if( $table_name != "members" ) {
                    $tbl_result = $model->query("SELECT `$table_name`.* FROM `memberz`.`$table_name` "
                        . "INNER JOIN `organisations`.`organisation_members` OM ON OM.member_id = $table_name.member_id AND OM.organisation_id = $organisation_id");
                }
                
                else {
                    $tbl_result = $model->query("SELECT `$table_name`.* FROM `memberz`.`$table_name` "
                        . "INNER JOIN `organisations`.`organisation_members` OM ON OM.member_id = $table_name.id AND OM.organisation_id = $organisation_id");
                }
                
                foreach($tbl_result as $tbl_row) {
                    $tbl_row_data = $tbl_row[$table_name];
                    $columns = array_keys($tbl_row_data);
                    $values = array_values($tbl_row_data);
                    array_walk($values, function(&$value) {
                        $value = mysql_escape_string($value);
                    });
                    
                    $col_string = "`" . implode("`,`", $columns) . "`";
                    $val_string = "'" . implode("','", $values) . "'";
                    
                    echo "REPLACE INTO `memberz`.`$table_name` ($col_string) VALUES ($val_string);\n";
                }
                
                echo "\n";
            }
            
            echo "SET FOREIGN_KEY_CHECKS = 1;\n";
            
            
            
            return;
        }
        
        $organisations = $this->Api->post('/organisation/listAll');
        $title_for_layout = "Data Management";
        
        $this->set(compact('title_for_layout', 'organisations'));
    }
    
    public function data_to_file() {
        
        if( isset($_POST['organisation_id']) ) {
            $this->autoRender = false;
            
            $model = ClassRegistry::init('Organisation');
            $organisation_id = $_POST['organisation_id'];
            
            $filename = ROOT . DS . APP_DIR . "/tmp/export_organisation_id_$organisation_id.sql";
            $file = fopen($filename, 'w');
            
            fwrite($file, "SET FOREIGN_KEY_CHECKS = 0; \n\n");

            // backup organisation specific data
            $result = $model->query("SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE (COLUMN_NAME IN ('organisation_id') AND TABLE_SCHEMA='organisations') OR (TABLE_NAME = 'organisations');");
            
            foreach($result as $row) {
                $table_name = $row['COLUMNS']['TABLE_NAME'];
                
                $tbl_result = null;
                
                if( $table_name != 'organisations' ) {
                    $tbl_result = $model->query("SELECT * FROM $table_name WHERE organisation_id = $organisation_id LIMIT 5000");
                }
                
                else {
                    $tbl_result = $model->query("SELECT * FROM $table_name WHERE id = $organisation_id");
                }
                
                foreach($tbl_result as $tbl_row) {
                    $tbl_row_data = $tbl_row[$table_name];
                    $columns = array_keys($tbl_row_data);
                    $values = array_values($tbl_row_data);
                    array_walk($values, function(&$value) {
                        $value = mysql_escape_string($value);
                    });
                    
                    $col_string = "`" . implode("`,`", $columns) . "`";
                    $val_string = "'" . implode("','", $values) . "'";
                    
                    fwrite($file, "REPLACE INTO `organisations`.`$table_name` ($col_string) VALUES ($val_string);\n");
                }
                
                fwrite($file, "\n");
            }
            
            // backup related member records
            $result2 = $model->query("SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE (COLUMN_NAME IN ('member_id') AND TABLE_SCHEMA='memberz') OR TABLE_NAME = 'members';");
            
            foreach($result2 as $row) {
                $table_name = $row['COLUMNS']['TABLE_NAME'];
                
                $tbl_result = null;
                
                if( $table_name != "members" ) {
                    $tbl_result = $model->query("SELECT `$table_name`.* FROM `memberz`.`$table_name` "
                        . "INNER JOIN `organisations`.`organisation_members` OM ON OM.member_id = $table_name.member_id AND OM.organisation_id = $organisation_id LIMIT 5000");
                }
                
                else {
                    $tbl_result = $model->query("SELECT `$table_name`.* FROM `memberz`.`$table_name` "
                        . "INNER JOIN `organisations`.`organisation_members` OM ON OM.member_id = $table_name.id AND OM.organisation_id = $organisation_id");
                }
                
                foreach($tbl_result as $tbl_row) {
                    $tbl_row_data = $tbl_row[$table_name];
                    $columns = array_keys($tbl_row_data);
                    $values = array_values($tbl_row_data);
                    array_walk($values, function(&$value) {
                        $value = mysql_escape_string($value);
                    });
                    
                    $col_string = "`" . implode("`,`", $columns) . "`";
                    $val_string = "'" . implode("','", $values) . "'";
                    
                    fwrite($file, "REPLACE INTO `memberz`.`$table_name` ($col_string) VALUES ($val_string);\n");
                }
                
                fwrite($file, "\n");
            }
            
            fwrite($file, "SET FOREIGN_KEY_CHECKS = 1;\n");
            
            fclose($file);
            
            return;
        }
        
        $organisations = $this->Api->post('/organisation/listAll');
        $title_for_layout = "Data Management";
        
        $this->set(compact('title_for_layout', 'organisations'));
    }
    
    public function users() {
        $title_for_layout = "User Management";
        
        $this->set(compact('title_for_layout'));
    }
    
    public function reports() {
        $title_for_layout = "System Reports";
        
        $this->set(compact('title_for_layout'));
    }
    
    public function images() {
        $title_for_layout = "Image Management";
        
        $this->set(compact('title_for_layout'));
    }
    
    public function organisation($organisation_id) {
        $this->autoRender = true;
        
        $this->layout = 'ajax';
        
        $organisation = $this->Api->post('/organisation', array(
            'organisation_id' => $organisation_id,
            'contain' => array(
                'OrganisationType', 'OrganisationSubscription' => array('SubscriptionType')
            )
        ));
        
        $stats = $this->Api->post("/support/organisation_stats", array(
            'organisation_id' => $organisation_id
        ));
        
        $this->set(compact('organisation', 'stats'));
    }
    
    public function organisation_activate() {
        $this->autoRender = false;
        
        if( isset($_POST['organisation_id']) && isset($_POST['activate']) ) {
            extract($_POST);
            
            $response = $this->Api->post('/organisation/activate', array(
                'organisation_id' => $organisation_id,
                'activate' => $activate == "true" ? 1 : 0
            ));
            
            return json_encode($response);
        }
    }
}
